package com.pubg.dao.impl;

import com.pubg.dao.BaseDao;
import com.pubg.dao.ProductDao;
import com.pubg.entity.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @描述 商品数据访问接口实现类
 * @Author WangYuanXiang
 * @Date 2019/4/26 14:26
 **/
public class ProductDaoImpl extends BaseDao implements ProductDao {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    @Override
    public int add(Product product) {
        String sql= "INSERT INTO product VALUES(0,?,?,?,?,?,?,?)";
        return executeUpdate(sql,
                    product.getName(),
                    product.getDescription(),
                product.getPrice(),
                product.getStock(),
                product.getCategoryLevel(),
                product.getFileName(),
                product.getCreateTime()
                );
    }

    @Override
    public int del(int productId) {
        String sql = "DELETE FROM product WHERE id=?";
        return executeUpdate(sql,productId);
    }

    @Override
    public int getCount(String name) {
        String sql =  "SELECT count(1) con FROM product where name like '%"+name+"%' ";
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()){
                return rs.getInt("con");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return 0;
    }

    @Override
    public List<Product> getPage(int currPageNo, int pageSize,String name) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT id,name,description,price,stock,categoryLevel,fileName,createTime " +
                "FROM `product` where name like '%"+name+"%' order by createTime desc LIMIT ?,?  ";
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,(currPageNo-1)*pageSize);
            pstmt.setInt(2,pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Product product = new Product(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getFloat("price"),
                        rs.getInt("stock"),
                        rs.getInt("categoryLevel"),
                        rs.getString("fileName"),
                        rs.getTimestamp("createTime")
                );
                list.add(product);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return list;
    }

    @Override
    public Product getOneProduct(int productId) {
        String sql = "SELECT id,name,description,price,stock,categoryLevel,fileName,createTime " +
                "FROM `product` where id =?";
        Product product = null;
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,productId);
            rs = pstmt.executeQuery();
            if (rs.next()){
                product = new Product(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getFloat("price"),
                        rs.getInt("stock"),
                        rs.getInt("categoryLevel"),
                        rs.getString("fileName"),
                        rs.getTimestamp("createTime")
                );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return product;
    }

    @Override
    public List<Product> findByName(String name) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT id,name,description,price,stock,categoryLevel,fileName,createTime " +
                "FROM `product` where name like concat('%',?,'%')";
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Product product = new Product(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getFloat("price"),
                        rs.getInt("stock"),
                        rs.getInt("categoryLevel"),
                        rs.getString("fileName"),
                        rs.getTimestamp("createTime")
                );
                list.add(product);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return list;
    }

    @Override
    public List<Product> list() {
        String sql = "SELECT id,name,description,price,stock,categoryLevel,fileName,createTime FROM product";
        List<Product> list = new ArrayList<>();
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Product product = new Product(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getFloat("price"),
                        rs.getInt("stock"),
                        rs.getInt("categoryLevel"),
                        rs.getString("fileName"),
                        rs.getTimestamp("createTime")
                );
                list.add(product);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return list;
    }
}
